﻿Imports System.Data.SqlClient

Public Class frmRenewalsUpdate

    Public SNo, OriginalSNo As Integer

    Sub FillInsuranceCompanies()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select CompanyName From InsuranceCompanies", cnn)
            Dim Reader As SqlDataReader

            Me.CombInsuranceCompany.Items.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Me.CombInsuranceCompany.Items.Add(Reader.Item(0))
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub FillClassofBusiness()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select ClassName From InsurClasses", cnn)
            Dim Reader As SqlDataReader

            Me.CombClassofBusiness.Items.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Me.CombClassofBusiness.Items.Add(Reader.Item(0))
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub frmRenewalsUpdate_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select * From ClientPolicies Where SNo=" & OriginalSNo, cnn)
            Dim Reader As SqlDataReader

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Me.txtClientID.Text = Reader.Item("ClientID")
                Me.txtClientName.Text = Reader.Item("ClientName")
                Me.CombInsuranceCompany.SelectedItem = Reader.Item("InsuranceCompany")
                Me.CombClassofBusiness.SelectedItem = Reader.Item("ClassOfBusiness")
                Me.txtPolicyNo.Text = Reader.Item("PolicyNo")
                Me.DTPPeriodFRm.Value = CDate(Reader.Item("IssueDate"))
                Me.DTPPeriodTo.Value = CDate(Reader.Item("ExpiryDate"))
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        If Me.CombInsuranceCompany.SelectedIndex = -1 OrElse Me.CombClassofBusiness.SelectedIndex = -1 OrElse Me.txtPolicyNo.Text.Trim.Length = 0 Then
            MsgBox("Please fill in all policy details")
        Else
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim cmd As New SqlCommand
                Dim Trans As SqlTransaction
                Dim Status As String

                If Me.RWon.Checked = True Then
                    Status = "Won"
                ElseIf Me.RLost.Checked = True Then
                    Status = "Loss"
                Else
                    MsgBox("Please choose if Won or Loss")
                    Me.Cursor = Cursors.Default
                    Exit Sub
                End If

                cnn.Open()
                Trans = cnn.BeginTransaction
                cmd.Connection = cnn
                cmd.Transaction = Trans

                'Save Contacts
                cmd.Parameters.Clear()
                cmd.CommandText = "Update ClientPolicies Set InsuranceCompany=@InsuranceCompany,ClassOfBusiness=@ClassOfBusiness," & _
                                  "PolicyNo=@PolicyNo,IssueDate=@IssueDate,ExpiryDate=@ExpiryDate,SavedUser=@SavedUser " & _
                                  "Where SNo=" & OriginalSNo

                cmd.Parameters.AddWithValue("@InsuranceCompany", Me.CombInsuranceCompany.SelectedItem)
                cmd.Parameters.AddWithValue("@ClassOfBusiness", Me.CombClassofBusiness.SelectedItem)
                cmd.Parameters.AddWithValue("@PolicyNo", Me.txtPolicyNo.Text.Trim)
                cmd.Parameters.AddWithValue("@IssueDate", Me.DTPPeriodFRm.Value)
                cmd.Parameters.AddWithValue("@ExpiryDate", Me.DTPPeriodTo.Value)
                cmd.Parameters.AddWithValue("@SavedUser", CurrentUser)

                cmd.ExecuteNonQuery()

                'Update if Won/Loss
                cmd.CommandText = "Update Renewal Set Status=N'" & Status & "' Where SNo=" & SNo
                cmd.ExecuteNonQuery()

                Trans.Commit()
                cnn.Close()

                MsgBox("Updated Successfully!", MsgBoxStyle.Information, Me.Text)

                Me.Cursor = Cursors.Default

                Me.Close()
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub
End Class